# /usr/bin/perl

use DBI;

# db に接続
$data_source = 'DBI:Pg:dbname=MarketDatabase';
$user_name = 'jiro';
$db = DBI->connect( $data_source, $user_name ) || die $db->errstr;

# 為替コードを取得
$sql = "select code from currency_exchange_type_ja_table;";
$st = $db->prepare( $ sql ) || die $db->errstr;
$st->execute() || die $db->errstr;
while ( $code = $st->fetchrow_array() ) {
	push( @currency_codes, $code );
}
$st->finish();

# 銘柄コードを取得
$sql = "select code from stock_code_ja_table where exist_flag = true;";
$st = $db->prepare( $sql ) || die $db->errstr;
$st->execute() || die $db->errstr;
while ( $code = $st->fetchrow_array() ) {
	push( @codes, $code );
}
$st->finish();

# スコアリング
foreach $currency_code ( @currency_codes ) {
	foreach $code ( @codes ) {
		# スコアを計算
		$sql = "select avg( coefficient ) from analyze_correlation_table where code1='$currency_code' and code2='$code' ";
		$sql .= "and start_date1 = start_date2 and end_date1 = end_date2 and differential = 0;";
#		print $sql . "\n";
		$st = $db->prepare( $sql );
		$st->execute();
		$score = $st->fetchrow_array();
		$st->finish();

		# スコアをDBに格納
		$sql = "insert into analyze_correlation_score_table values( '$code', 0, '$currency_code', '0', $score );";
		$st = $db->prepare( $sql );
		$st->execute();
	}
}

$st->finish();
$db->disconnect();

$s = 'fuck';
print "$s you?\n";

